<?php
/** * search_model Class * 
 * @package TIAP 
 * @subpackage Model 
 * @category search 
 * @author Harits Hamid Balfas 
 * @link http://twitter.com/jeblagsancang 
 */ 
class Search_model extends MY_Model{

	function Search_model(){
		parent::MY_Model();
		$this->crime_type_table = 'crime_type';
		$this->crime_report_table = 'crime_report';
		$this->crime_type_category_table = 'crime_type_category';
		$this->directory_company_table = 'directory_company';
		$this->alerts_table = 'alerts';
	}

	/*
	 * method get_crime_report_search
	 * @description get list of crime report search from database from keyword
	 * @parent $limit = jumlah fetch
	 * @parent $offset = start fetch
	 * @return array/false
	 */
	function get_crime_report_search($search_value='', $limit='10', $offset='0'){
		if(!empty($search_value)){
			$this->db->select("{$this->crime_report_table}.* ,
			{$this->crime_type_table}.name as crime_type_name,
			{$this->crime_type_category_table}.name as crime_category_name,
			{$this->crime_type_category_table}.icon as icon ");
			$this->db->join($this->crime_type_table, "{$this->crime_report_table}.crime_type = {$this->crime_type_table}.id", 'left');
			$this->db->join($this->crime_type_category_table, "{$this->crime_type_table}.crime_type_id = {$this->crime_type_category_table}.id", 'left');
			$this->db->where("{$this->crime_report_table}.approved",'yes');
			$this->db->like("{$this->crime_report_table}.tags",$search_value);
			$this->db->or_like("{$this->crime_report_table}.street_address",$search_value);
			$this->db->or_like("{$this->crime_report_table}.city_address",$search_value);
			$this->db->or_like("{$this->crime_report_table}.detail",$search_value);
			$this->db->order_by("{$this->crime_report_table}.id",'DESC');
			$query = $this->db->get($this->crime_report_table,$limit,$offset);
			//echo $this->db->last_query();
			if($query->num_rows() > 0){
				return $query->result_array();
			}else{
				return FALSE;
			}
		}else{
			return FALSE;	
		}
	}
	
	/**
	 * total_crime_report_search
	 * get total data in table crime report, based on particular search keyword
	 *
	 * @return total result
	 */
	function total_crime_report_search($search_value) {
		if(!empty($search_value)){
			$this->db->from($this->crime_report_table);
			$this->db->join($this->crime_type_table, "{$this->crime_report_table}.crime_type = {$this->crime_type_table}.id", 'left');
			$this->db->join($this->crime_type_category_table, "{$this->crime_type_table}.crime_type_id = {$this->crime_type_category_table}.id", 'left');
			$this->db->where("{$this->crime_report_table}.approved",'yes');
			$this->db->like("{$this->crime_report_table}.tags",$search_value);
			$this->db->or_like("{$this->crime_report_table}.street_address",$search_value);
			$this->db->or_like("{$this->crime_report_table}.city_address",$search_value);
			$this->db->or_like("{$this->crime_report_table}.detail",$search_value);
			return $this->db->count_all_results();
		}else{
			return FALSE;
		}
	}
	/**
	 * function network_search
	 * get data network that like search value
	 * @param $search
	 * @param $limit
	 * @param $offset
	 */
	function network_search($search = '', $limit = '', $offset = '0') {
		if(!empty($search)){
	        $this->db->select('network.name as name, network.slug_name as slug_name, user_location.city as city');
	        $this->db->from('network');
	        $this->db->join('user_location', 'network.id = user_location.user_tiab_id', 'left');	        
	        $this->db->like('network.name', $search);
	        $this->db->or_like('user_location.city', $search);
	        $this->db->or_like('network.description', $search);	
	        $this->db->like('network.status', 1);
	        if($limit) {
	            $this->db->limit($limit, $offset);
	        }
	        //$this->db->query('AND network.status = 1');        
	        $query = $this->db->get();
	        //echo $this->db->last_query();
	        return $query->result_array();
		}else{
			return FALSE;	
		}
    }
    /**
     * function total_network_search
     * get total data result from network search
     * @param $search
     */
	function total_network_search($search = '') {
		if(!empty($search)){
	        $this->db->select('network.name as name, user_location.city as city');
	        $this->db->from('network');
	        $this->db->join('user_location', 'network.id = user_location.user_tiab_id', 'left');	                
	        $this->db->like('network.name', $search);
	        $this->db->or_like('user_location.city', $search);
	        $this->db->or_like('network.description', $search);
	        $this->db->like('network.status', 1);	        	             
	        return $this->db->count_all_results();
		}else{
			return FALSE;	
		}
    }
    /**
     * function directory_search
     * get directory data result from search
     * @param $search
     * @return array result
     */
    function directory_search($search = ''){
    	if(!empty($search)){
	        $this->db->select('company, city_name, province_name');
	        $this->db->from($this->directory_company_table);
	        if($search) {
	            $this->db->like('company', $search);
	            $this->db->or_like('company_description', $search);
	            $this->db->or_like('city_name', $search);	
	            $this->db->or_like('province_name', $search);            
	        }	              
	        if($limit) {
	            $this->db->limit($limit, $offset);
	        }
	        //$this->db->query('AND network.status = 1');        
	        $query = $this->db->get();
	        //echo $this->db->last_query();
	        return $query->result_array();
		}else{
			return FALSE;	
		}
    }
    /**
     * function total_directory_search
     * get total data directory result from search
     * @param $search
     */
	function total_directory_search($search = ''){
    	if(!empty($search)){	        
	        $this->db->from($this->directory_company_table);
	        if($search) {
	            $this->db->like('company', $search);
	            $this->db->or_like('company_description', $search);
	            $this->db->or_like('city_name', $search);	
	            $this->db->or_like('province_name', $search);            
	        }
	        return $this->db->count_all_results();
		}else{
			return FALSE;	
		}    	
    }
    /**
     * function news_search
     * get news data that match the search value 
     * @param $search
     * @return array result
     */
	function news_search($search = '', $user_tiab_id = '') {
		if(!empty($search)){
			$newssql = "SELECT `news`.`id` AS news_id, article_heading,`news`.`id_user_tiab` as `user_id`,";
			$newssql .= " concat_ws( ' ', `news`.`posted_date` , `news`.`posted_time` ) AS `created_date` , ";
			$newssql .= "view_count, fb_like_count, 1 AS news_type, comm_count.comment_count";
			$newssql .= " FROM `news`";
			$newssql .= " LEFT JOIN `news_comments` ON `news`.`id` = `news_comments`.`tiab_news_id`";
			$newssql .= " LEFT JOIN (SELECT count(*) as comment_count, id FROM `news_comments` WHERE `news_comments`.`tiab_news_id` = '1' GROUP BY id) as comm_count ON `news`.`id` = comm_count.id";
			if(!empty($user_tiab_id)){
				$newssql .= " WHERE `news`.`id_user_tiab` = '".$user_tiab_id."'"; 
				$newssql .= " AND `news`.`news_visibility` IN ('1', '2', '3')";	
			}else{
				$newssql .= " WHERE `news`.`news_visibility` IN ('2', '3')";	
			}			
			$newssql .= " AND `news`.`article_heading` LIKE '%".$search."%'";
			$newssql .= " OR `news`.`article_description` LIKE '%".$search."%'";			
			$orderby = " ORDER BY `created_date` DESC";
				
			$newssql = $newssql.$orderby;
			$query = $this->db->query($newssql);
			//echo $this->db->last_query();
			if($query->num_rows() > 0){
				return $query->result_array();
			}else{
			  	return false;
			}
		}else{
			return false;	
		}
    }
    /**
     * function total_news_search
     * get total result data from searc value 
     * @param $search
     */
	function total_news_search($search = '', $user_tiab_id = '') {
		if(!empty($search)){
			$newssql = "SELECT count(`news`.`id`) as total";		
			$newssql .= " FROM `news`";
			$newssql .= " LEFT JOIN `news_comments` ON `news`.`id` = `news_comments`.`tiab_news_id`";
			if(!empty($user_tiab_id)){
				$newssql .= " WHERE `news`.`id_user_tiab` = '".$user_tiab_id."'"; 
				$newssql .= " AND `news`.`news_visibility` IN ('1', '2', '3')";	
			}else{
				$newssql .= " WHERE `news`.`news_visibility` IN ('2', '3')";	
			}			
			$newssql .= " AND `news`.`article_heading` LIKE '%".$search."%'";
			$newssql .= " OR `news`.`article_description` LIKE '%".$search."%'";			
			$query = $this->db->query($newssql);
			//echo $this->db->last_query();
			if($query->num_rows() > 0){
				return $query->row_array();
			}else{
			  	return false;
			}
		}else{
			return false;
		}
    }
    /**
     * function search_alerts
     * get alert data if user has login and match the search value
     * @param $search
     * @param $user_tiab_id
     */
	function alerts_search($search='', $user_tiab_id=''){    
		if(!empty($user_tiab_id )){			
			$this->db->where('id_user', $user_tiab_id);
			$this->db->like('short_alert', $search);
			$this->db->or_like('alert_detail', $search);
			$query = $this->db->get($this->alerts_table);
			if($query->num_rows() > 0){
				return $query->result_array();
			}else{
				return FALSE;
			}
		}else{
			return FALSE;	
		}	    
	}
	/**
	 * function total_alerts_search
	 * get total data alerts that match the search value
	 * @param $search
	 * @param $user_tiab_id
	 */
	function total_alerts_search($search='', $user_tiab_id=''){    
		if(!empty($user_tiab_id )){
			$this->db->from($this->alerts_table);
			$this->db->where('id_user', $user_tiab_id);
			$this->db->like('short_alert', $search);
			$this->db->or_like('alert_detail', $search);			
			return $this->db->count_all_results();			
		}else{
			return FALSE;	
		}	    
	}
}